package com.iris.live.services.data.repositories.impl;

import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;

/**
 * @Description:金融漏斗定时任务
 * @author: Tony.Zhang
 * @Date: 2017-10-06 10:14
 **/
@Repository("FinancialFunnelCrontabRepositoryImpl")
public class FinancialFunnelCrontabRepositoryImpl extends CrontabRepositoryImpl {
    public FinancialFunnelCrontabRepositoryImpl() {
    }

    @Override
    public void execute(Map<String, Object> context) throws Exception {
        //报价金融数
        this.updateQuotationFinancialNum(context);

        //订单金融申请数
        this.updateOrderFinancialApplicationNum(context);

        //零售金融申请数
        this.updateRetailFinancialApplicationNum(context);
    }

    /**
     * 报价金融数
     * @param context
     * @throws Exception
     */
    private void updateQuotationFinancialNum(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_rpt_data_finance_sale_info( " +
                "`permit`, `sale_month`, `vehicle_model_id`, `fin_org_id`, `fin_loan_type`, `fin_quotation_num`) " +
                "    SELECT a.`许可` permit, DATE_FORMAT(:v_endDate,'%Y%m') sale_month, a.`意向车型A` vehicle_model_id, fin_org_setting.`金融机构编号`, " +
                "       IF(quote_fin.`金融机构` IN ('奔驰租赁','利星行租赁','易汇资本'),'租赁','贷款') type, " +
                "       IFNULL(SUM(IF(a.`是否首次到店`='是' AND a.`是否报价`='是' AND NOT ISNULL(a.`顾客编号`) AND a.`客户级别` NOT LIKE 'E%',1,0)),0) `fin_quotation_num` " +
                "    FROM `顾客进店记录表` a JOIN ( " +
                "         SELECT MIN(进店编号) 进店编号 " +
                "         FROM  `顾客进店记录表` a " +
                "         WHERE  a.`进店日期` >= :v_startDate and a.`进店日期` <= :v_endDate " +
                "           AND a.`到店时间`!='00:00:00' " +
                "           AND a.`到店时间`!='01:00:00' " +
                "           AND a.`到店时间`!='03:00:00'" +
                "           AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是')) " +
                "           AND NOT ISNULL(a.`意向车型A`) " +
                "           GROUP BY a.`顾客编号` " +
                "    ) c ON a.`进店编号`=c.`进店编号` " +
                "    LEFT JOIN `报价单表` quote ON a.`报价编号`=quote.`报价编号` " +
                "    LEFT JOIN `报价金融表` quote_fin ON quote.`金融`=quote_fin.`金融编号` " +
                "    LEFT JOIN `lsh金融机构设置` fin_org_setting ON quote_fin.`金融机构`=fin_org_setting.`金融机构` AND (a.`许可`=fin_org_setting.`许可` OR fin_org_setting.`许可`='LSH') " +
                "    WHERE a.`进店日期`>=:v_startDate AND a.`进店日期`<=:v_endDate " +
                "    AND NOT ISNULL(quote_fin.`贷款额`) AND a.`许可` IS NOT NULL AND a.`意向车型A` IS NOT NULL " +
                "    AND quote_fin.`贷款额`>0 " +
                "    AND NOT ISNULL(fin_org_setting.`金融机构编号`) " +
                "    GROUP BY a.`许可`,a.`意向车型A`,fin_org_setting.`金融机构编号` " +
                "    ON DUPLICATE KEY UPDATE  fin_quotation_num=VALUES(fin_quotation_num)";

        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 订单金融申请数
     * @param context
     * @throws Exception
     */
    private void updateOrderFinancialApplicationNum(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_rpt_data_finance_sale_info( " +
                "`permit`, `sale_month`, `vehicle_model_id`, `fin_org_id`, `fin_loan_type`, `fin_application_num`) " +
                "    SELECT a.`许可` permit, DATE_FORMAT(:v_endDate,'%Y%m') sale_month, a.`车型编号` vehicle_model_id, fin_org_setting.`金融机构编号`, " +
                "       IF(quote_fin.`金融机构` IN ('奔驰租赁','利星行租赁','易汇资本'),'租赁','贷款') type, " +
                "       IFNULL(SUM(IF(DATE_FORMAT(c.`订单审批通过日期`,'%Y-%m-%d')>=:v_startDate AND DATE_FORMAT(c.`订单审批通过日期`,'%Y-%m-%d')<=:v_endDate ,1,0)),0) -" +
                "       IFNULL(SUM(IF(DATE_FORMAT(c.`订单失销日期`,'%Y-%m-%d')>=:v_startDate AND DATE_FORMAT(c.`订单失销日期`,'%Y-%m-%d') <= :v_endDate,1,0)),0) `fin_application_num`" +
                "    FROM `报价单表` a  JOIN vw_max_quoted_code b ON a.`报价编号`=b.`报价编号` " +
                "                      JOIN `报价订单表` c ON a.`订单编号`=c.`订单编号` " +
                "                      LEFT JOIN `报价交车表` d ON a.`交车`=d.`编号` " +
                "                      LEFT JOIN `报价金融表` quote_fin ON a.`金融`=quote_fin.`金融编号` " +
                "                      LEFT JOIN `lsh金融申请` fin_approval ON a.`初始报价单号`=fin_approval.`初始报价单号`" +
                "                      LEFT JOIN `lsh金融机构设置` fin_org_setting ON quote_fin.`金融机构`=fin_org_setting.`金融机构` AND (a.`许可`=fin_org_setting.`许可` OR fin_org_setting.`许可`='LSH') " +
                "    WHERE NOT ISNULL(fin_org_setting.`金融机构编号`) AND quote_fin.`贷款额`>0 AND " +
                "      ((c.订单审批通过日期 <= CONCAT(:v_endDate,' 23:59:59')  AND c.订单审批通过日期 >= CONCAT(:v_startDate,' 00:01:01')) OR (c.订单失销日期 <= CONCAT(:v_endDate, ' 23:59:59')  AND c.订单失销日期 >= CONCAT(:v_startDate,' 00:01:01')))  " +
                "    GROUP BY a.`许可`,a.`车型编号`,fin_org_setting.`金融机构编号` " +
                "    ON DUPLICATE KEY UPDATE  fin_application_num=VALUES(fin_application_num)";

        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 零售金融申请数
     * @param context
     * @throws Exception
     */
    private void updateRetailFinancialApplicationNum(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_rpt_data_finance_sale_info( " +
                "`permit`, `sale_month`, `vehicle_model_id`, `fin_org_id`, `fin_loan_type`, `fin_application_retail_num`, `fin_passed_retail_num`) " +
                "    SELECT a.`许可` permit, DATE_FORMAT(:v_endDate,'%Y%m') sale_month, a.`车型编号` vehicle_model_id, fin_org_setting.`金融机构编号`, " +
                "       IF(quote_fin.`金融机构` IN ('奔驰租赁','利星行租赁','易汇资本'),'租赁','贷款') type, " +
                "       IFNULL(SUM(IF(DATE_FORMAT(d.`开票时间`, '%Y-%m-%d')>=:v_startDate AND DATE_FORMAT(d.`开票时间`, '%Y-%m-%d')<=:v_endDate AND NOT ISNULL(fin_approval.`申请日期`) ,1,0)),0) -" +
                "       IFNULL(SUM(IF(DATE_FORMAT(d.`退票时间`, '%Y-%m-%d')>=:v_startDate AND DATE_FORMAT(d.`退票时间`, '%Y-%m-%d')<=:v_endDate,1,0)),0) `fin_application_retail_num`," +
                "       IFNULL(SUM(IF(DATE_FORMAT(d.`开票时间`, '%Y-%m-%d')>=:v_startDate AND DATE_FORMAT(d.`开票时间`, '%Y-%m-%d')<=:v_endDate AND fin_approval.`状态` IN ('07','10','11') ,1,0)),0) -" +
                "       IFNULL(SUM(IF(DATE_FORMAT(d.`退票时间`, '%Y-%m-%d')>=:v_startDate AND DATE_FORMAT(d.`退票时间`, '%Y-%m-%d')<=:v_endDate,1,0)),0) `fin_passed_retail_num` " +
                "    FROM `报价单表` a  JOIN vw_max_quoted_code b ON a.`报价编号`=b.`报价编号` " +
                "                           LEFT JOIN `报价交车表` d ON a.`交车`=d.`编号` " +
                "                           LEFT JOIN `报价金融表` quote_fin ON a.`金融`=quote_fin.`金融编号` " +
                "                           LEFT JOIN `lsh金融申请` fin_approval ON a.`初始报价单号`=fin_approval.`初始报价单号`" +
                "                           LEFT JOIN `lsh金融机构设置` fin_org_setting ON quote_fin.`金融机构`=fin_org_setting.`金融机构` AND (a.`许可`=fin_org_setting.`许可` OR fin_org_setting.`许可`='LSH') " +
                "    WHERE NOT ISNULL(fin_org_setting.`金融机构编号`) AND " +
                "     ((d.开票时间 <= CONCAT(:v_endDate, ' 23:59:59')  AND d.开票时间 >= CONCAT(:v_startDate,' 00:01:01'))  " +
                "      OR (d.退票时间 <= CONCAT(:v_endDate, ' 23:59:59')  AND d.退票时间 >= CONCAT(:v_startDate,' 00:01:01'))) " +
                "    GROUP BY a.`许可`,a.`车型编号`,fin_org_setting.`金融机构编号` " +
                "    ON DUPLICATE KEY UPDATE  fin_application_retail_num=VALUES(fin_application_retail_num), fin_passed_retail_num=VALUES(fin_passed_retail_num)";

        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }
}
